﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    public class DA_WMG003:DA_Matrix
    {
        public DA_WMG003()
        {
            tableName = "WMG.dbo.WMG002";
        }

        public DataTable GetSummaryData(string p_ym)
        {
            FillTableSchema(tableName);
            string sql = getSummaryStatement();
            FillFieldList(new string[] { p_ym }, new string[] { "@YM" }, "年月");
            AddParameter(sql);
            return getData();
        }

        public override string[] GetDistinctField()
        {
            return new string[] { "年月" };
        }

        protected string getSummaryStatement()
        {
            return @"SELECT
	CASE WHEN A.存款類別 IS NOT NULL THEN '' ELSE A.版塊別 END 版塊別	
	,CASE WHEN A.存款類別 IS NULL THEN '' ELSE A.存款類別 END 存款類別
	,CONVERT(VARCHAR, CAST(A.DUE_AMT AS MONEY ),1)  DUE_AMT
	,CONVERT(VARCHAR, CAST(A.OPN_AMT AS MONEY ),1)  OPN_AMT
	,CASE 
		WHEN A.OPN_AMT = 0 THEN '0%' 
		ELSE 			
			CAST(ROUND(A.OPN_AMT/A.DUE_AMT,4)*100 AS VARCHAR) + '%'
		END RATE	
FROM(
	SELECT 
		'公庫' AS 版塊別
		,A.存款類別
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 100
			WHEN A.存款類別 = '整存整付' THEN 110
			WHEN A.存款類別 = '存本取息' THEN 120
			WHEN A.存款類別 = '外匯定期' THEN 130
			ELSE 90 END ORD
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('13','12')
		AND A.年月 = @YM
		AND A.存款類別 IN ('定期存款','整存整付','存本取息','外匯定期')
	GROUP BY CUBE(A.存款類別)

	UNION ALL

	SELECT 
		'財管' AS 版塊別
		,A.存款類別
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 150
			WHEN A.存款類別 = '整存整付' THEN 160
			WHEN A.存款類別 = '存本取息' THEN 170
			WHEN A.存款類別 = '外匯定期' THEN 180
			ELSE 140 END ORD
		
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('A1')
		AND A.年月 = @YM
		AND A.存款類別 IN ('定期存款','整存整付','存本取息','外匯定期')	
	GROUP BY CUBE(A.存款類別)

	UNION ALL

	SELECT 
		'信託' AS 版塊別
		,A.存款類別
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 200
			WHEN A.存款類別 = '整存整付' THEN 210
			WHEN A.存款類別 = '存本取息' THEN 220
			WHEN A.存款類別 = '外匯定期' THEN 230
			ELSE 190 END ORD	
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('A5')
		AND A.年月 = @YM
		AND A.存款類別 IN ('定期存款','外匯定期')
	GROUP BY CUBE(A.存款類別)

	UNION ALL

	SELECT 
		'企金' AS 版塊別
		,A.存款類別
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 250
			WHEN A.存款類別 = '整存整付' THEN 260
			WHEN A.存款類別 = '存本取息' THEN 270
			WHEN A.存款類別 = '外匯定期' THEN 280
			ELSE 240 END ORD	
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('C1')
		AND A.年月 = @YM
		AND A.存款類別 IN  ('定期存款','整存整付','存本取息','外匯定期')
	GROUP BY CUBE(A.存款類別)

UNION ALL

SELECT 
	'總續存率' AS 版塊別
	,A.存款類別
	,SUM(A.DUE_AMT) AS DUE_AMT
	,SUM(A.OPN_AMT) AS OPN_AMT
	,CASE 
		WHEN A.存款類別 = '定期存款' THEN 310
		WHEN A.存款類別 = '整存整付' THEN 320
		WHEN A.存款類別 = '存本取息' THEN 330
		WHEN A.存款類別 = '外匯定期' THEN 340
		ELSE 300 END ORD	
FROM WMG.dbo.WMG002 A
WHERE A.年月 = @YM
	AND A.存款類別 IN  ('定期存款','整存整付','存本取息','外匯定期')
GROUP BY CUBE(A.存款類別)


)A ORDER BY ORD";
        }
    }
}
